튜닝과 치료(157p)

• 문제 발생
• 문제점 파악
  1) Explain Plan으로 실행계획 확인(X-Ray 촬영)
  2) AutoTrace로 실제 일량 측정(CT 촬영)
  3) SQL 트레이스로 내부 수행 절차의 부하 발생 단계 확인(MRI촬영)

• 문제점 해결
  1) 쿼리 변환(외용약 처방), 옵티마이져 힌트사용(내복약 처방)
  2) 인덱스 조정(시술)
  3) 반정규화, 집계 테이블 생성(수술)

Explain plan(158p~160p)

(1) plan_table 생성하기

• explain plan : SQL을 수행하기 전에 실행계획을 확인 할 때 사용하는 명령어
• explain plan 을 사용하기 위해서는 plan_table을 생성해야한다.

SQL> @?/rdbms/admin/utlxplan.sql   -- ? 는 $ORACLE HOME 디렉토리 대체하는 기호

 
SQL> SELECT owner, synonym_name, table_owner, table_name
  2    FROM all_synonyms
  3   WHERE synonym_name = 'PLAN_TABLE'
  4  ;

OWNER    SYNONYM_NAME    TABLE_OWNER    TABLE_NAME
------- -------------- ---------------- -----------------
PUBLIC   PLAN_TABLE      SYS            PLAN_TABLE$               

• explain plan for 명령을 수행하고 나면 해당 SQL에 대한 실행계획이 plan_table 에 저장된다.

 
SQL> explain plan set statement id = 'queryl' for  --  statement id = 'queryl'  생략가능
   2 select * from emp where empno = 7900;

해석되었습니다.

h5.(2) plan_table 포맷팅

• plan_table에 저장된 정보를 포맷팅 SQL
• AutoTrace 또는 TOAD에서 실행 계획 출력시 명령 수행 후 plan_table의 데이터를 포맷팅해서 출력.

 
SQL> SELECT lpad(id, 4, ' ') || NVL(LPAD(parent_id, 6, ' '), '       ')
  2      || ' ' || lpad(' ', (LEVEL - 1) * 2, ' ')
  3      || operation || NVL2(options, ' ( ' || options || ' ) ', '')
  4      || NVL2(object_name, ' OF '''
  5      || object_owner || '.' || object_name, NULL)
  6      || NVL2(object_name, '''', '')
  7      || decode(parent_id, NULL, ' Optimizer=' || optimizer)
  8      || (CASE
  9            WHEN cost IS NULL AND cardinality IS NULL AND bytes IS NULL
 10            THEN ''
 11            ELSE '(' || NVL2(cost, 'Cost=' || cost, '')
 12                     || NVL2(cardinality, 'Card=' || cardinality, '')
 13                     || NVL2(bytes, 'Bytes=' || bytes, '')
 14                     || ')' END) "Execution Plan"
 15  FROM   plan_table p
 16  START WITH statement_id = 'query1' AND id = 0
 17  CONNECT BY PRIOR id = parent_id AND PRIOR statement_id = statement_id
 18  ORDER BY id;


Execution Plan
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=32)
1  0  TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=32)
2  1   INDEX (UNIQUE SCAN) OF 'EMP_PK' (Cost=0 Card=1)

• utlxpls.sql / utlxplp.sql 사용 (9i 부터 사용가능)

SQL> SET LINESIZE 200
SQL> @?/rdbms/admin/utlxpls

---------------------------------------------------------------------------------------
| Id | Operation                    | Name   | Rows | Bytes | Cost ( %CPU) |
|  0 | SELECT STATEMENT             |        |    1 |    32 |    1     (0) |
|  1 |  TABLE ACCESS BY INDEX ROWID | EMP    |    1 |    32 |    1     (0) |
|* 2 |   INDEX UNIQUE SCAN          | EMP_PK |    1 |       |    0     (0) |
---------------------------------------------------------------------------------------

PLAN TABLE OUTPUT
------------------------------
Plan hash value : 4024650034

Predicate Information (identified by operation id) :
2 - access ( "EMPNO" =7900 )

(3) SQL Repository 실행계획 모니터링

• SQL Repository의 SQL에 대해 매일 explain plan 명령 수행하여 별도 테이블에 실행계획을 저장해 두면 안정적인 시스템 운영 및 성능관리에 활용 가능함.

예를 들어，인텍스 구조를 바꾸고자 할 때 해당 인텍스를 시용하는 쿼리 목록을 뽑아 사 전점검을 실시할 수 있다. 또는 오브젝트 통계정보가 바뀌어 어느 날 갑자기 성능이 나빠진 쿼리가 생겼을 때 이전 실행계획을 빨리 확인하고 예전과 같은 방식으로 수행되도록 빠르게 튜닝할 수 있다.

• sql_repository에 저장된 SQL을 sql_plan_repository라고 명명된 plan table에 실행계획을 저장하는 스크립트 예시.
  
  Warning
  sql_repository 테이블은 쉽게 예시하려고 만든 것일 뿐이며, 실제 운영 환경에서 SQL 문장을 테이블에 담아두고 수행할때마다 "매번 실시간으로" 읽어오는 방식을 사용하는 것은 금물.
  

  CREATE TABLE SQL repository(SQL id VARCHAR2(30), SQL text VARCHAR2(4000));
  BEGIN
    FOR c IN (SELECT sql_id, sql_text frαn sql_repository) 
    LOOP
      EXECUTE IMMEDIATE 'explain plan set statement_id = ''' || c.sql_id 
              || ''' into sql_plan_repository' 
              || ' for ' || c.sql_text;
      COMMIT;
    END LOOP;
  END;
  /